﻿using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using WebApplication1.Models;
using System.Data.SqlClient;
using Microsoft.AspNetCore.Hosting.Server;
using Microsoft.Extensions.Hosting.Internal;
using Microsoft.AspNetCore.Http;
namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        
        private readonly ILogger<HomeController> _logger;
       

        private string strconn = "Server=localhost;Database=SchoolDB;User Id = sa; Password=123456;";

 

        public HomeController(ILogger<HomeController> logger)
        {
            _logger = logger;
        }

        public IActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public IActionResult loginfrom(string username,string password)
        {
            HttpContext.Session.SetString("CurrentUser", username);
            return RedirectToAction("Privacy");
         
        }
       
        [HttpPost]
        public string addStudents([FromBody] Student s)
        {
            if (s != null)
            {
                using (SqlConnection conn = new SqlConnection(strconn))
                {
                    string sql = "INSERT INTO student ( name, age, grade,local) VALUES ( @Name, @Age, @Class,@Local)";
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.Parameters.AddWithValue("@Name", s.Name ?? " ");
                    comm.Parameters.AddWithValue("@Age", s.Age ?? " ");
                    comm.Parameters.AddWithValue("@Class", s.Grade ?? " ");
                    comm.Parameters.AddWithValue("@Local", s.Local ?? " ");
                    try
                    {
                        conn.Open();
                        int rowsAffected = comm.ExecuteNonQuery();
                        return "添加成功";
                    }
                    catch (Exception ex)
                    {
                        // 记录或返回错误信息
                        return $"添加失败: {ex.Message}";
                    }
                }
            }
            return "添加失败: 学生对象为空";
        }
        [HttpPost]
        public string delStudents([FromBody] Student s)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strconn))
                {
                    string sql = "DELETE FROM student WHERE id = @Id";
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.Parameters.AddWithValue("@Id", s.Name);

                    conn.Open();
                    int rowsAffected = comm.ExecuteNonQuery();
                    if (rowsAffected > 0)
                    {
                        return "删除成功";
                    }
                    else
                    {
                        return "删除失败: 未找到匹配的记录";
                    }
                }
            }
            catch (Exception ex)
            {
                return $"删除失败: {ex.Message}";
            }
        }
        [HttpPost]
        public string editStudents([FromBody] Student s)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(strconn))
                {
                    string sql = "UPDATE student SET name =@Name,age =@Age,grade =@Grade,local =@Local WHERE id = @Id";
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.Parameters.AddWithValue("@Id", s.Id);
                    comm.Parameters.AddWithValue("@Name", s.Name ?? " ");
                    comm.Parameters.AddWithValue("@Age", s.Age ?? " ");
                    comm.Parameters.AddWithValue("@Grade", s.Grade ?? " ");
                    comm.Parameters.AddWithValue("@Local", s.Local ?? " ");
                    conn.Open();
                    int rowsAffected = comm.ExecuteNonQuery();
                    if (rowsAffected > 0)
                    {
                        return "修改成功";
                    }
                    else
                    {
                        return "修改失败: 未找到匹配的记录";
                    }
                }
            }
            catch (Exception ex)
            {
                return $"修改失败: {ex.Message}";
            }
        }

        [HttpPost]
        public List<Student> listStudents([FromBody] Student s)
        {
            var students = new List<Student>();

            using (var conn = new SqlConnection(strconn))
            {
                string sql = "";
                if (s.Name == "")
                {
                    sql = "SELECT Id, Name,Age,Grade,Local FROM Student ";
                }
                else
                {
                    sql = "SELECT Id, Name,Age,Grade,Local FROM Student where name like '%"+s.Name+"%'";
                }
                
                var comm = new SqlCommand(sql, conn);

                try
                {
                    conn.Open();
                    using (var reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new Student
                            {
                                Id = reader.GetInt32(0),  
                                Name = reader.GetString(1),
                                Age = reader.GetString(2),
                                Grade = reader.GetString(3),
                                Local = reader.GetString(4)
                            });
                        }
                    }
                }
                catch (Exception ex)
                {
 
                    throw new ApplicationException("获取学生列表失败: " + ex.Message, ex);
                }
            }

            return students;
        }
        [HttpGet]
        public List<StudentView> listViews()
        {
            var students = new List<StudentView>();

            using (var conn = new SqlConnection(strconn))
            {
                const string sql = "SELECT local,COUNT(*) FROM Student GROUP BY local";
                var comm = new SqlCommand(sql, conn);

                try
                {
                    conn.Open();
                    using (var reader = comm.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            students.Add(new StudentView
                            {
                                 
                                Name = reader.GetString(0),
                                Value = reader.GetInt32(1)
                                 
                            });
                        }
                    }
                }
                catch (Exception ex)
                {

                    throw new ApplicationException("获取学生列表失败: " + ex.Message, ex);
                }
            }

            return students;
        }
        [HttpGet]
        public StudentView user()
        {
            string username = HttpContext.Session.GetString("CurrentUser");
            return new StudentView { Name = username };
        }
        public IActionResult Privacy()
        {
            return View();
        }

        [ResponseCache(Duration = 0, Location = ResponseCacheLocation.None, NoStore = true)]
        public IActionResult Error()
        {
            return View(new ErrorViewModel { RequestId = Activity.Current?.Id ?? HttpContext.TraceIdentifier });
        }
    }
}
